export function getQueryVideoField(wx_user: string, tableName, profit_count?: string, other_filed?: string, level?: number): {sql: string, beforeParams: string[], afterParams: string[]}{
  
  let sql =  `
      video.id,
      video.id_card,
      video.video_url,
      video.video_cover,
      video.video_ratio as video_ratio,
      (select sum(unit_price) from reward_success where reward_success.video_id = video.id) as isreward,
      (select case when unit_price is null then 30 else unit_price end from reward_success where reward_success.video_id = video.id and reward_success.wx_user = video.wx_user_source) as question_price,
      (select case when unit_price = 20000 then 1 else 0 end from reward_success where reward_success.video_id = video.id limit 0,1) as question_require_price,
      video.type,
      video.original,
      (select count(1) from multi_img where video_id = video.id) as multi_img_count,
      (select lv.id from wx_like_video as lv where lv.video_id = video.id and lv.wx_user = ? and lv.delete_tag = 0 limit 0,1) as like_count,
      (select lv.source from wx_like_video as lv where lv.video_id = video.id and lv.wx_user = ? and lv.delete_tag = 0 limit 0,1) as collectionSource,
      (
        select wx_user.id from wx_like_video
        inner join wx_user on wx_user.openid = wx_like_video.share_wx_user
        where wx_like_video.video_id = video.id and wx_like_video.wx_user = ?
        limit 0,1
      ) as collection_share_wx_user_id,
      (
        select profit.id from profit
        inner join answer on answer.id = profit.answer_id
        inner join wx_like_video on wx_like_video.video_id = answer.video_id and wx_like_video.share_wx_user = profit.use_wx_user
        where wx_like_video.video_id = video.id and profit.payment_on_behalf is null and profit.status != 4 and wx_like_video.wx_user = ?
        limit 0,1
      ) as collection_share_profit,
      (
        select answer.id from answer
        inner join wx_like_video on wx_like_video.video_id = answer.video_id and wx_like_video.share_wx_user = answer.wx_user
        where wx_like_video.video_id = video.id and answer.check = 1 and answer.delete_tag = 0 and wx_like_video.wx_user = ?
        limit 0,1
      ) as collection_share_answer,
      (
        select profit.id from profit
        inner join answer on answer.id = profit.answer_id
        inner join wx_like_video on wx_like_video.video_id = answer.video_id
        where wx_like_video.video_id = video.id and profit.payment_on_behalf is not NULL and profit.status != 4
        limit 0,1
      ) as collection_payment_on_behalf_profit,
      video.ic_group,
      video.check,
      video.wx_user as video_author,
      video.overseas,
      video.wx_user_source,
      reward_success.unit_price as userRewardPrice,
      video.profit_type as video_profit_type,
      ${
        level == 1 ? '' : `
          wx_user_ic.id as wx_user_id,
          wx_user_ic.name as ic_name,
          wx_user_ic.avatar_img,
          (
            select v.id from video as v
            inner join internet_celebrity_group on internet_celebrity_group.id = v.ic_group
            where v.id = video.id and v.check = 1 and (v.ic_group in (
              select internet_celebrity_group.id as ic_group from internet_celebrity_group
              left join video as v on v.ic_group = internet_celebrity_group.id
              left join platform on platform.ic_group = internet_celebrity_group.id
              where internet_celebrity_group.type = 2 
            ) or (v.overseas = 1 and v.original = 1)) and v.overseas = 1 and v.original = 1 and internet_celebrity_group.type = 2
          ) as authen_work,
          (
            select v.id from video as v
            inner join platform on platform.ic_group = v.ic_group
            inner join internet_celebrity_group on internet_celebrity_group.id = v.ic_group
            where v.id = video.id and (
              (
                (
                  (
                    internet_celebrity_group.type = 0 and 
                    (
                      platform.platform in (select setting.value from setting where setting.key = 'level3')
                      or (select count(1) from platform where platform.platform != '' and platform.ic_group = internet_celebrity_group.id) = 0
                    )
                  )
                  or internet_celebrity_group.type = 3
                  or (v.overseas = 3 and internet_celebrity_group.type = 0 and v.original = 1)
                )
                and (
                  -- 提问图片
                  (
                    v.original = 0 and v.type = 1
                  )
                  -- 视频库图片
                  or (
                    v.overseas = 0 and v.type = 1
                  )
                  -- 网红被删除
                  or internet_celebrity_group.delete_tag = 1
                )
              )
            )
            limit 0,1
          ) as otherNetwork,
          (
            select v.id from video as v
            inner join platform on platform.ic_group = v.ic_group
            inner join internet_celebrity_group on internet_celebrity_group.id = v.ic_group
            where 
              (
                (
                  internet_celebrity_group.type = 0 and 
                  (
                    platform.platform in (select setting.value from setting where setting.key = 'level3')
                    or (select count(1) from platform where platform.platform != '' and platform.ic_group = internet_celebrity_group.id) = 0
                  )
                )
                or internet_celebrity_group.type = 3
                or (v.overseas = 3 and internet_celebrity_group.type = 0 and v.original = 1)
              )
              and v.id = video.id
              and 
                (
                  -- 提问视频，进入网红主页
                  (
                    v.type = 0 and v.original = 0
                  )
                  -- 非认证作品，进入网红主页
                  or (
                    v.overseas = 3
                  )
                  -- 认证作品
                  or (
                    v.overseas = 1
                  )
                  -- 视频库视频
                  or (
                    v.overseas = 0 and v.type = 0
                  )
                )
            limit 0,1
          ) as otherNetwork_ic_info,
          (
            select v.id from video as v
            left join platform on platform.ic_group = v.ic_group
            left join internet_celebrity_group on internet_celebrity_group.id = v.ic_group
            where 
              (
                (
                  (
                    (
                      internet_celebrity_group.type = 0 and (
                        platform.platform in (select setting.value from setting where setting.key = 'level3')
                        or (select count(1) from platform where platform.platform != '' and platform.ic_group = internet_celebrity_group.id) = 0
                      )
                    )
                    or (
                      (
                        internet_celebrity_group.type = 3 
                        or internet_celebrity_group.type = 2
                        or (v.overseas = 3 and internet_celebrity_group.type = 0 and v.original = 1)
                      )
                      and v.source != 2
                    )
                  ) 
                )
                or (
                  v.overseas = 3 and v.ic_group is null and v.original = 1 and v.profit_type = 1
                )
              )
              and v.id = video.id
              and 
                (
                  -- 提问视频，进入网红主页
                  (
                    v.type = 0 and v.original = 0 and v.in_ic = 1
                  )
                  -- 非认证作品，进入网红主页
                  or (
                    v.overseas = 3 and v.profit_type = 1
                  )
                  -- 认证作品，看完整版
                  or (
                    v.overseas = 1 and v.profit_type = 1
                  )
                  -- 视频库视频
                  or (
                    v.overseas = 0 and v.type = 0 and (v.in_ic = 1 or v.in_ic is null)
                  )
                )
            limit 0,1
          ) as is_look_full_video,
          (
            select v.id from video as v
            inner join platform on platform.ic_group = v.ic_group
            inner join internet_celebrity_group on internet_celebrity_group.id = v.ic_group
            where 
              (
                (
                  internet_celebrity_group.type = 0 and (
                    platform.platform in (select setting.value from setting where setting.key = 'level3')
                    or (select count(1) from platform where platform.platform != '' and platform.ic_group = internet_celebrity_group.id) = 0
                  )
                )
                or internet_celebrity_group.type = 3
                or internet_celebrity_group.type = 2
                or (v.overseas = 3 and internet_celebrity_group.type = 0 and v.original = 1)
                or v.ic_group in (
                  select ic_group from internet_celebrity_group_rela_ic_info
                  inner join internet_celebrity_group on internet_celebrity_group.id = internet_celebrity_group_rela_ic_info.ic_group_rela_ic_info
                  where internet_celebrity_group.type = 2
                )
              )
              and v.id = video.id
              and 
                (
                  -- 提问视频，不进入网红主页
                  (
                    v.original = 0 and (
                      (v.source = 2 and (v.in_ic = 0 or v.in_ic is null)) or 
                      (v.source = 0 and v.in_ic = 0)
                    )
                  )
                  -- 非认证作品，不进入网红主页，看出处
                  or (
                    v.overseas = 3 and v.profit_type = 0
                  )
                  -- 认证作品，看出处
                  or (
                    v.overseas = 1 and v.profit_type = 0
                  )
                  -- 视频库视频，不进入主页
                  or (
                    v.overseas = 0 and (v.in_ic = 0)
                  )
                  or v.ic_group in (
                    select ic_group from internet_celebrity_group_rela_ic_info
                    inner join internet_celebrity_group on internet_celebrity_group.id = internet_celebrity_group_rela_ic_info.ic_group_rela_ic_info
                    where internet_celebrity_group.type = 2
                  )
                )
            limit 0,1
          ) as is_look_answer,
          (
            select count(1) from internet_celebrity 
            inner join platform on platform.ic_group = internet_celebrity.ic_group
            where internet_celebrity.ic_group = video.ic_group and platform.platform is not null and platform.platform != ''
          ) as is_other_platform,
          (
            select count(1) from video as v
            inner join internet_celebrity_group on internet_celebrity_group.id = v.ic_group
            where v.ic_group = video.ic_group and (
              (
                internet_celebrity_group.type = 0 and (
                  v.ic_group in (select ic_group from platform inner join setting on setting.value = platform.platform where setting.key = 'level3')
                  or (select count(1) from platform where platform.platform != '' and platform.ic_group = internet_celebrity_group.id) = 0
                )
              )
              or (
                (
                  internet_celebrity_group.type = 3 
                  or internet_celebrity_group.type = 2
                  or (v.overseas = 3 and internet_celebrity_group.type = 0 and v.original = 1)
                )
                and v.source != 2
              )
            )
            and v.check = 1
            and 
              (
                -- 提问视频，进入网红主页
                (
                  v.type = 0 and v.original = 0 and v.in_ic = 1
                )
                -- 非认证作品，进入网红主页
                or (
                  v.overseas = 3 and v.profit_type = 1 and v.in_ic = 1
                )
                -- 认证作品，看完整版
                or (
                  v.overseas = 1 and v.profit_type = 1 and (v.in_ic = 1 or v.in_ic is null)
                )
                -- 视频库视频
                or (
                  v.overseas = 0 and v.type = 0 and (v.in_ic = 1 or v.in_ic is null)
                )
              )
          ) as count_look_full_video,
          (
            select count(1) 
            from profit_unlock_home 
            where profit_unlock_home.video_id = video.id and profit_unlock_home.use_wx_user = ? and profit_unlock_home.status != 4 and profit_unlock_home.video_id = video.id
          ) is_unlock_home,
          (
            select profit_unlock_home.create_time
            from profit_unlock_home 
            where profit_unlock_home.video_id = video.id and profit_unlock_home.use_wx_user = ? and profit_unlock_home.status != 4 and profit_unlock_home.video_id = video.id
            order by profit_unlock_home.create_time desc
            limit 0,1
          ) is_unlock_home_time,
          (
            select profit.create_time from profit
            inner join answer on answer.id = profit.answer_id
            where answer.video_id = video.id and answer.check = 1 and profit.use_wx_user = ? and profit.status != 4 and answer.video_id = video.id
              and (profit.unit_price > 0 || profit.status = 0) and profit.payment_on_behalf is null
            order by profit.id desc
            limit 0,1
          ) as profit_type_time,
          (
            select profit_unlock_home.status
            from profit_unlock_home 
            where profit_unlock_home.video_id = video.id and profit_unlock_home.use_wx_user = ? and profit_unlock_home.video_id = video.id
          ) unlock_profit_status,
          (
            select internet_celebrity.avatar_img from internet_celebrity where internet_celebrity.ic_group = video.ic_group
            order by internet_celebrity.id asc
            limit 0,1
          ) as avatar_img_ic,
          (
            select internet_celebrity.name from internet_celebrity where internet_celebrity.ic_group = video.ic_group
            order by internet_celebrity.id asc
            limit 0,1
          ) as ic_name_ic,
          (
            select id from concern where concern_wx_user_id = wx_user_ic.id and concern.wx_user = ? and concern.wx_user is not null
            limit 0,1
          ) as concern_wx_user_concern_id,
          (
            select count(1) from answer_report_errors
            where answer_report_errors.answer_id = b.id and answer_report_errors.wx_user = ? and answer_report_errors.reset_error = 0
          ) as is_report_error,
          b.wx_user as big_reward_answer,
          wx_user.id as video_user_id,
        `
      }
      video.check_text,
      (
        select id from concern where concern_ic_group = video.ic_group and  concern.wx_user = ? and concern.wx_user is not null
        limit 0,1
      ) as concern_ic_group_concern_id,
      (
        select count(1) from questions where questions.video_id = video.id and questions.wx_user = ? and questions.delete_tag = 0
      ) as isQuestion,
      (
        select sum(1) from questions where questions.video_id = video.id and questions.delete_tag = 0
      ) as question_count,
      (
        select profit.type from profit 
        inner join answer on answer.id = profit.answer_id and answer.delete_tag = 0
        where answer.check = 1 and profit.status != 4 and answer.video_id = video.id and profit.use_wx_user = ? and (profit.unit_price > 0 || profit.status = 0)
        limit 0,1
      ) as profit_type,
      (
        select profit.type from profit
        where profit.payment_on_behalf in (
          select profit.id from profit 
          inner join answer on answer.id = profit.answer_id and answer.delete_tag = 0
          where answer.check = 1 and profit.status != 4 and answer.video_id = video.id and profit.use_wx_user = ?
        )
        limit 0,1
      ) as payment_on_behalf_type,
      (
        select profit.type from profit 
        inner join answer on answer.id = profit.answer_id and answer.delete_tag = 0
        where answer.check = 1 and profit.status != 4 and answer.video_id = video.id and profit.use_wx_user = ?
          and profit.payment_on_behalf is not null and profit.payment_on_behalf != ''
        limit 0,1
        ) as payment_on_behalf_type_is_me,
      (
        select count(1) from answer
        where answer.video_id = video.id and answer.delete_tag = 0 and answer.wx_user = ?
          and (
            answer.check = 0
          )
      ) as answer_reject,
      (
        select count(1) from answer
        where answer.video_id = video.id and answer.delete_tag = 1 and answer.wx_user = ?
          and (
            answer.check = 0
          )
      ) as answer_reject_delete,
      (
        select internet_celebrity_group.id from internet_celebrity_group 
        where internet_celebrity_group.type = 2 and internet_celebrity_group.id in (
          select internet_celebrity_group_rela_ic_info.ic_group_rela_ic_info from internet_celebrity_group_rela_ic_info
          where internet_celebrity_group_rela_ic_info.ic_group = video.ic_group
        )
      ) as lib_rela_ic_info,
      (
        select wx_user.avatar_img from internet_celebrity_group 
        inner join wx_user on wx_user.openid = internet_celebrity_group.wx_user
        where internet_celebrity_group.type = 2 and internet_celebrity_group.id in (
          select internet_celebrity_group_rela_ic_info.ic_group_rela_ic_info from internet_celebrity_group_rela_ic_info
          where internet_celebrity_group_rela_ic_info.ic_group = video.ic_group
        )
      ) as lib_rela_ic_info_avatar_img,
      (
        select wx_user.name from internet_celebrity_group 
        inner join internet_celebrity_group_rela_ic_info on internet_celebrity_group_rela_ic_info.ic_group_rela_ic_info = internet_celebrity_group.id
        inner join wx_user on wx_user.openid = internet_celebrity_group.wx_user
        where internet_celebrity_group_rela_ic_info.ic_group = video.ic_group
      ) as ic_name_rela_ic_info,
      ${profit_count || `(
        select count(1) from profit b
        inner join answer c on c.id = b.answer_id and c.delete_tag = 0
        inner join video d on d.id = c.video_id
        where d.id = video.id and b.unit_price > 0
      ) as profit_count`}
      ${other_filed || ''}
    from ${tableName}
    ${
      level == 1 ? '' : `
        inner join answer as b on b.video_id = video.id and b.check = 1 and b.delete_tag = 0
        inner join wx_user on wx_user.openid = video.wx_user
        inner join internet_celebrity_group on internet_celebrity_group.id = video.ic_group
        inner join wx_user wx_user_ic on wx_user_ic.openid = internet_celebrity_group.wx_user
        left join concern on concern.wx_user = ? and concern.concern_wx_user_id = wx_user_ic.id
      `
    }
    left join reward_success on reward_success.video_id = video.id and reward_success.wx_user = ?
    `
  let beforeParams = [wx_user, wx_user, wx_user, wx_user, wx_user, wx_user, wx_user, wx_user, wx_user, wx_user, wx_user]
  if(level != 1){
    let wxp = [wx_user, wx_user, wx_user, wx_user, wx_user, wx_user, wx_user]
    beforeParams.push(...wxp)
  }
  let afterParams = [wx_user, wx_user]
  return { sql, beforeParams, afterParams }
}

export async function getVideoOtherParams(videoRepository, ids, findAnswerIds?: any, wx_user?: string){
  if(ids.length === 0){
    return {answerList: [], multiImg: []}
  }
  let sql = []
  ids.map(e => {
    sql.push(` b.video_id = ? `)
  })
  let sqlText = ''
  if(sql.length > 0){
    sqlText = ` and (${sql.join(' or ')})`
  }

  let sqlText_Answer = ''
  if(findAnswerIds){
    let sqlAnswer = []
    findAnswerIds.map(e => {
      sqlAnswer.push(` b.video_id = ? `)
    })
    if(sqlAnswer.length > 0){
      sqlText_Answer = ` and (${sqlAnswer.join(' or ')})`
    }
  }else{
    sqlText_Answer = sqlText 
  }

  let answerList: any  = []
  if(findAnswerIds && findAnswerIds.length === 0){
    answerList = []
  }else{
    answerList = await videoRepository.query(`
      select b.id,b.video_id,b.wx_user,b.create_time,b.check,b.check_time,b.type,b.answer_template_id
        ,b.id as answer_id,c.use_wx_user,e.platform,e.ic_name,e.img,e.img_id, c.status as profit_status, b.id_card as id_card,b.reject_auto_time
      from answer b
      left join profit as c on c.answer_id = b.id
      left join answer_info as e on e.answer_id = b.id
      where b.delete_tag = 0 ${sqlText_Answer}
      order by e.create_time desc
    `,findAnswerIds || ids)
  }

  let multiImg = await videoRepository.query(`
    select distinct * from (
      select b.*,multi_img.img as multi_img,b.id as answer_id, '' as use_wx_user,multi_img.cover,multi_img.id as multi_img_id
      from multi_img
      inner join answer as b on b.video_id = multi_img.video_id
      where b.check = 1 and b.wx_user = ? and b.delete_tag = 0 ${sqlText}
      union all
      select b.*,multi_img.img as multi_img,b.id as answer_id,profit.use_wx_user,multi_img.cover,multi_img.id as multi_img_id
      from multi_img
      inner join answer as b on b.video_id = multi_img.video_id
      inner join profit on profit.answer_id = b.id
      where b.check = 1 and profit.use_wx_user = ? ${sqlText}
    )a
    order by a.cover desc,a.multi_img_id asc
  `,[wx_user, ...ids, wx_user, ...ids])


  return {answerList, multiImg}
}

export function getOtherNetwork_ic_info(){
  return `(
    select v.id from video as v
    inner join platform on platform.ic_group = v.ic_group
    inner join internet_celebrity_group on internet_celebrity_group.id = v.ic_group
    where v.id = video.id 
      and (
        internet_celebrity_group.type = 0
        and (
          (
            platform.platform in (select setting.value from setting where setting.key = 'level3')
            or (select count(1) from platform where platform.platform != '' and platform.ic_group = internet_celebrity_group.id) = 0
          )
          or (v.overseas = 3 and v.original = 1)
        )
      )
      and (
        v.overseas = 3
        or (v.original = 0 and v.type = 0)
      )
      
    limit 0,1
  ) as otherNetwork_ic_info`
}
export function videoFields(tableName){
  tableName = tableName ? tableName + '.' : ''
  return ' \
    ' + tableName + '`id`, \
    ' + tableName + '`video_id`, \
    ' + tableName + '`video_url`, \
    ' + tableName + '`video_url_source`, \
    ' + tableName + '`video_cover`, \
    ' + tableName + '`create_time`, \
    ' + tableName + '`update_time`, \
    ' + tableName + '`video_key`, \
    ' + tableName + '`video_ratio`, \
    ' + tableName + '`ic_group`, \
    ' + tableName + '`wx_user`, \
    ' + tableName + '`check`, \
    ' + tableName + '`type`, \
    ' + tableName + '`original`, \
    ' + tableName + '`check_time`, \
    ' + tableName + '`source`, \
    ' + tableName + '`profit_type`, \
    ' + tableName + '`check_text`, \
    ' + tableName + '`check_text_time`, \
    ' + tableName + '`trace_id`, \
    ' + tableName + '`filemd5`, \
    ' + tableName + '`isreward`, \
    ' + tableName + '`overseas`, \
    ' + tableName + '`delete_tag`, \
    ' + tableName + '`id_card`, \
    ' + tableName + '`is_home`, \
    ' + tableName + '`wx_user_source`, \
    ' + tableName + '`in_ic`, \
    ' + tableName + '`ic_group_source`, \
    ' + tableName + '`reject_reason`, \
    ' + tableName + '`use_id`'
}